Excel VBA Programming For Dummies, 3rd Edition by John Walkenbach

Author:John Walkenbach
Language: eng
Format: epub, pdf
Publisher: John Wiley and Sons, Inc.
Published: 2013-01-30T16:00:00+00:00

Figure 11-5: When a chart sheet is activated, the user sees a message like this.

A workbook that contains this code is available at this book’s website.

Dim OldSheet As Object

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)

Set OldSheet = Sh

End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

Dim Msg As String

If TypeName(Sh) = "Chart" Then

Msg = "This chart contains "

Msg = Msg & ActiveChart.SeriesCollection(1).Points.Count

Msg = Msg & " data points." & vbNewLine

Msg = Msg & "Click OK to return to " & OldSheet.Name

MsgBox Msg

OldSheet.Activate

End If

End Sub

Workbook activation events

Excel also recognizes the event that occurs when you activate or deactivate a particular workbook. The following code, which is contained in the Code window for the ThisWorkbook object, is executed whenever the workbook is activated. The procedure simply maximizes the workbook’s window.

Private Sub Workbook_Activate()

ActiveWindow.WindowState = xlMaximized

End Sub

An example of Workbook_Deactivate code appears next. This procedure is executed when a workbook is deactivated. This procedure copies the selected range whenever the workbook is deactivated. It might be useful if you’re copying data from lots of different areas and pasting them to a different workbook. When this event-procedure is in place, you can select the range to be copied, activate the other workbook, select the destination, and press Ctrl+V (or Enter) to paste the copied data.

Private Sub Workbook_Deactivate()

ThisWorkbook.Windows(1).RangeSelection.Copy

End Sub

Simple as it is, this procedure required some experimentation before I got it to work correctly. First I tried this:

Selection.Copy

This statement didn’t work as I had intended. It copied the range from the second workbook (the one I activated after deactivating the first workbook). That’s because the second workbook became the active workbook after the deactivation event occurred.

This statement didn’t work either. In fact, it gave me a runtime error:

ThisWorkbook.ActiveSheet.Selection.Copy

I eventually remembered the RangeSelection property of a Window object, and that one did the trick.

Other Worksheet-Related Events

In the preceding section, I present examples for worksheet activation and deactivation events. In this section, I discuss three additional events that occur in worksheets: double-clicking a cell, right-clicking a cell, and changing a cell.

The BeforeDoubleClick event

You can set up a VBA procedure to be executed when the user double-clicks a cell. In the following example (which is stored in the Code window for a Sheet object), double-clicking a cell in that sheet makes the cell bold (if it’s not bold) or not bold (if it is bold):

Private Sub Worksheet_BeforeDoubleClick _

(ByVal Target As Excel.Range, Cancel As Boolean)

Target.Font.Bold = Not Target.Font.Bold

Cancel = True

End Sub

The Worksheet_BeforeDoubleClick procedure has two arguments: Target and Cancel. Target represents the cell (a Range object) that was double-clicked. If Cancel is set to True, the default double-click action doesn’t occur.

The default action for double-clicking a cell is to put Excel into cell edit mode. I didn’t want that to happen, so I set Cancel to True.

The BeforeRightClick event

The BeforeRightClick event is similar to the BeforeDoubleClick event, except that it consists of right-clicking a cell. The following procedure checks to see whether the cell that was right-clicked contains a numeric value.

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.